﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NRemedy;
using NRemedy.Linq;
using System.Configuration;
using Oracle.DataAccess.Client;

namespace Syncer
{
    public class ProblemImport
    {
        public Dictionary<string, Int32> _ComDic { get; set; }

        public Dictionary<string, Int32> _BsDic { get; set; }

        public void Execute(Param param)
        {
            Log.Info("start problem import.");
            //get application config
            string arserver = ConfigurationManager.AppSettings["ARServer"].ToString();
            string user = ConfigurationManager.AppSettings["User"].ToString();
            string pwd = ConfigurationManager.AppSettings["Pwd"].ToString();

            _ComDic = param.ComDic;
            _BsDic = param.BsDic;

            using (ARLoginContext context = new ARLoginContext(arserver, user, pwd))
            {
                ARSet<PBMProblemInvestigation> problems = new ARSet<PBMProblemInvestigation>(context, new ARServerDefaultFactory());
                var query = from i in problems
                            where i.Modifed_Date >= param.StartTime && i.Modifed_Date <= param.EndTime
                            select i;

                using (OracleDataAccess oda = new OracleDataAccess())
                {
                    foreach (var pbm in query)
                    {
                        string sql = "select Request_ID from Problem where Request_ID=:requestId";
                        //incidentNumber must not be null
                        OracleParameter[] _param = new OracleParameter[] { 
                            new OracleParameter("requestId",pbm.ProblemInvestigationID)
                        };
                        object requestid = oda.ExecuteScalar(sql, _param);
                        //if not exsit,should be insert
                        if (requestid == null)
                        {
                            try
                            {
                                InsertProblem(pbm, oda);
                                Log.Info("Insert " + pbm.ProblemInvestigationID + " OK.");
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("Insert " + pbm.ProblemInvestigationID + " failed.", ex);
                            }
                        }
                        else
                        {
                            try
                            {
                                //update incident
                                UpdateProblem(pbm, oda);
                                Log.Info("Update " + pbm.ProblemInvestigationID + " OK.");
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("Update " + pbm.ProblemInvestigationID + " failed.", ex);
                            }
                        }
                    }
                }

            }
            Log.Info("problem import end.");
        }


        private void InsertProblem(PBMProblemInvestigation pbm, OracleDataAccess oda)
        {
            string insert_sql = @"insert into Problem(
                                    Request_ID,
                                    SiteID,
                                    BusinessSystemId,
                                    Status,
                                    TicketCreateTime,
                                    DiagnoseTime,
                                    Impact,
                                    HasPrimaryCause,
                                    CloseCode,
                                    FirmProStatus,
                                    ResolvedTimeout,
Firm,
Priority,
ProblemSource
)
                            values (
                                        :request_id,
                                        :siteid,
                                        :businesssystemid,
                                        :status,
                                        :ticketcreatetime,
                                        :diagnoseTime,
                                        :impact,
                                        :hasPrimaryCause,
                                        :closecode,
                                        :firmProStatus,
                                        :resolvedtimeout,
:vendor,
:priority,
:problemsource
                                    )";
            OracleParameter[] param = new OracleParameter[] { 
                            new OracleParameter("request_id",pbm.ProblemInvestigationID),
                            //new OracleParameter("siteid",companyDic[incident.Company]),
                            //new OracleParameter("businesssystemid",string.IsNullOrEmpty(incident.BusinessSystem) ? null : (object)bsDic[incident.BusinessSystem + incident.Company]),
                            new OracleParameter("status",(int)pbm.Status),
                            new OracleParameter("impact",(int)pbm.Impact),
                            new OracleParameter("closecode",(int)pbm.OverCode),
                            new OracleParameter("ticketcreatetime",pbm.Create_Date),
                            new OracleParameter("diagnoseTime",pbm.Closed_Date),
                            new OracleParameter("hasPrimaryCause",pbm.PrimaryCause == null ? 0 : 1),
                            new OracleParameter("firmProStatus",(int)pbm.Vendor_Satus),
                            new OracleParameter("resolvedtimeout",null),
                            new OracleParameter("vendor",pbm.cChr_Vendor),
                            new OracleParameter("priority",(int)pbm.Priority),
                             new OracleParameter("problemsource",(int)pbm.Problem_Source)
                        };
            var plist = param.ToList();

            //find companyid
            if (!_ComDic.ContainsKey(pbm.ContactCompany))
                throw new Exception("Can not match " + pbm.ContactCompany);
            plist.Add(new OracleParameter("siteid", _ComDic[pbm.ContactCompany]));


            //find businesssystemid
            if (string.IsNullOrEmpty(pbm.SystemB))
                plist.Add(new OracleParameter("businesssystemid", null));
            else if (!_BsDic.ContainsKey(pbm.SystemB + pbm.ContactCompany))
                throw new Exception("Can not match " + pbm.SystemB + pbm.ContactCompany);
            else
                plist.Add(new OracleParameter("businesssystemid", _BsDic[pbm.SystemB + pbm.ContactCompany]));

            ////calculate resolvedtimeout
            //if (pbm.LastResolvedDate == default(DateTime) || incident.ResovledTimeLimit == default(decimal))
            //    plist.Add(new OracleParameter("resolvedtimeout", null));
            //else
            //    plist.Add(new OracleParameter("resolvedtimeout", (incident.LastResolvedDate - incident.Create_Date).TotalSeconds - (double)incident.ResovledTimeLimit));

            oda.ExecuteCommand(insert_sql, plist.ToArray());
        }

        private void UpdateProblem(PBMProblemInvestigation pbm, OracleDataAccess oda)
        {
            string update_sql = @"update Problem set
                                    SiteID=:siteid,
                                    BusinessSystemId=:businesssystemid,
                                    Status=:status,
                                    TicketCreateTime=:ticketcreatetime,
                                    DiagnoseTime=:diagnoseTime,
                                    Impact=:impact,
                                    HasPrimaryCause=:hasPrimaryCause,
                                    CloseCode=:closecode,
                                    FirmProStatus=:firmProStatus,
                                    ResolvedTimeout=:resolvedtimeout,
Firm=:vendor,
Priority=:priority,
ProblemSource=:problemsource
                                    where Request_ID=:request_id
                                    ";
            OracleParameter[] param = new OracleParameter[] { 
                             new OracleParameter("request_id",pbm.ProblemInvestigationID),
                            //new OracleParameter("siteid",companyDic[incident.Company]),
                            //new OracleParameter("businesssystemid",string.IsNullOrEmpty(incident.BusinessSystem) ? null : (object)bsDic[incident.BusinessSystem + incident.Company]),
                            new OracleParameter("status",(int)pbm.Status),
                            new OracleParameter("impact",(int)pbm.Impact),
                            new OracleParameter("closecode",(int)pbm.OverCode),
                            new OracleParameter("ticketcreatetime",pbm.Create_Date),
                            new OracleParameter("diagnoseTime",pbm.Closed_Date),
                            new OracleParameter("hasPrimaryCause",pbm.PrimaryCause == null ? 0 : 1),
                            new OracleParameter("firmProStatus",(int)pbm.Vendor_Satus),
                            new OracleParameter("resolvedtimeout",null),
                            new OracleParameter("vendor",pbm.cChr_Vendor),
                            new OracleParameter("priority",(int)pbm.Priority),
                             new OracleParameter("problemsource",(int)pbm.Problem_Source)
                        };
            var plist = param.ToList();

            //find companyid
            if (!_ComDic.ContainsKey(pbm.ContactCompany))
                throw new Exception("Can not match " + pbm.ContactCompany);
            plist.Add(new OracleParameter("siteid", _ComDic[pbm.ContactCompany]));


            //find businesssystemid
            if (string.IsNullOrEmpty(pbm.SystemB))
                plist.Add(new OracleParameter("businesssystemid", null));
            else if (!_BsDic.ContainsKey(pbm.SystemB + pbm.ContactCompany))
                throw new Exception("Can not match " + pbm.SystemB + pbm.ContactCompany);
            else
                plist.Add(new OracleParameter("businesssystemid", _BsDic[pbm.SystemB + pbm.ContactCompany]));

            ////calculate resolvedtimeout
            //if (pbm.LastResolvedDate == default(DateTime) || incident.ResovledTimeLimit == default(decimal))
            //    plist.Add(new OracleParameter("resolvedtimeout", null));
            //else
            //    plist.Add(new OracleParameter("resolvedtimeout", (incident.LastResolvedDate - incident.Create_Date).TotalSeconds - (double)incident.ResovledTimeLimit));

            oda.ExecuteCommand(update_sql, plist.ToArray());
        }


    }
}
